package leaveSystem.dao.mysql;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;

import leaveSystem.dao.DAOException;

import leaveSystem.dao.ViewLeaveDAO;
import leaveSystem.model.EmpLeave;
import leaveSystem.model.Leave;

public class ViewLeaveDAOImpl implements ViewLeaveDAO {
	private static final String dbUrl = "jdbc:mysql://localhost:3306/leaveapp";
	private static final String dbUserName = "root";
	private static final String dbPassword = "password";
	private Connection con;
	private Statement st;

	// Open the Connection & Statement with DB
	public void openConnection() throws ClassNotFoundException, SQLException {
		Class.forName("com.mysql.jdbc.Driver");
		con = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
		st = con.createStatement();
	}

	// Close the Connection & Statement with DB
	public void closeConnection() throws SQLException {
		st.close();
		con.close();
	}

	public ArrayList<EmpLeave> getApplicationForApproval(int mgrid)
			throws DAOException {
		ArrayList<EmpLeave> empleaves = new ArrayList<EmpLeave>();
		try {
			openConnection();
			String sql = " SELECT l.*,lt.`leaveTypename`,e.`name` ";
			sql += "FROM `leaveapp`.`leave` l ";
			sql += "INNER JOIN `leaveapp`.`leave_type` lt ON l.`leave_type_id`=lt.`leaveTypeid` ";
			sql += "INNER JOIN `leaveapp`.`employee` e ON l.`employee_id`=e.`employeeid` ";
			sql += "WHERE e.`manager_id`=" + mgrid + " ";
			sql += "AND l.`leave_startDate` > now()";
			sql += "AND (`status`='Applied' OR `status`='Updated') ";
			sql += "GROUP BY l.`employee_id` ORDER BY e.`name`;";

			ResultSet rs = st.executeQuery(sql);
			System.out.println("SQL: " + sql);
			while (rs.next()) {
				EmpLeave empleave = new EmpLeave();
				empleave.setLeaveId(rs.getInt("leave_id"));
				empleave.setEmployeeId(rs.getInt("employee_id"));
				empleave.setEmployeeName(rs.getString("name"));
				empleave.setLeaveType(rs.getString("leaveTypename"));
				empleave.setLeaveStartDate(rs.getDate("leave_startDate"));
				empleave.setLeaveEndDate(rs.getDate("leave_endDate"));
				System.out.println(empleave);
				empleaves.add(empleave);
			}
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new leaveSystem.dao.DAOException(error);
		} finally {
			try {
				closeConnection();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return empleaves;
	}

	public EmpLeave getEmpLeave(int leaveid) throws DAOException {
		EmpLeave empleave = new EmpLeave();
		try {
			openConnection();
			String sql = " SELECT l.*,lt.`leaveTypename`,e.`name` ";
			sql += "FROM `leaveapp`.`leave` l ";
			sql += "INNER JOIN `leaveapp`.`leave_type` lt ON l.`leave_type_id`=lt.`leaveTypeid` ";
			sql += "INNER JOIN `leaveapp`.`employee` e ON l.`employee_id`=e.`employeeid` ";
			sql += "WHERE l.`leave_id`=" + leaveid + " ";

			System.out.println("SQL: " + sql);
			ResultSet rs = st.executeQuery(sql);
			while (rs.next()) {
				empleave.setLeaveId(rs.getInt("leave_id"));
				empleave.setEmployeeId(rs.getInt("employee_id"));
				empleave.setEmployeeName(rs.getString("name"));
				empleave.setLeaveType(rs.getString("leaveTypename"));
				empleave.setLeaveStartDate(rs.getDate("leave_startDate"));
				empleave.setLeaveEndDate(rs.getDate("leave_endDate"));
				empleave.setLeaveReason(rs.getString("leave_reason"));
				empleave.setStatus(rs.getString("status"));
				empleave.setRemainingLeaves(rs.getInt("remaining_leaves"));
				empleave.setCreatedBy(rs.getString("createdBy"));
				empleave.setCreatedOn(rs.getDate("createdOn"));
				empleave.setModifiedBy(rs.getString("modifiedBy"));
				empleave.setModifiedOn(rs.getDate("modifiedOn"));
				empleave.setRemarks(rs.getString("remarks"));
				System.out.println(empleave);
			}
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new leaveSystem.dao.DAOException(error);
		} finally {
			try {
				closeConnection();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return empleave;
	}

	public ArrayList<EmpLeave> getLeaveOnSamePeriod(EmpLeave empleave, int mgrid)
			throws DAOException {
		ArrayList<EmpLeave> leavesonsameperiod = new ArrayList<EmpLeave>();
		try {
			openConnection();
			String sql = " SELECT l.*,lt.`leaveTypename`,e.`name` ";
			sql += "FROM `leaveapp`.`leave` l ";
			sql += "INNER JOIN `leaveapp`.`leave_type` lt ON l.`leave_type_id`=lt.`leaveTypeid` ";
			sql += "INNER JOIN `leaveapp`.`employee` e ON l.`employee_id`=e.`employeeid` ";
			sql += "WHERE '" + empleave.getLeaveStartDate()
					+ "' <= l.`leave_endDate` ";
			sql += "AND l.`leave_startDate` <= '" + empleave.getLeaveEndDate()
					+ "' ";
			sql += "AND e.`manager_id`=" + mgrid + " ";
			sql += "AND l.`leave_id` != " + empleave.getLeaveId() + " ";
			sql += "AND l.`status` != 'Rejected' AND l.`status` != 'Deleted' AND l.`status` != 'Cancelled';";

			System.out.println("SQL: " + sql);
			executeQuery(leavesonsameperiod, sql);
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new leaveSystem.dao.DAOException(error);
		} finally {
			try {
				closeConnection();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return leavesonsameperiod;
	}

	@Override
	public void submitDecision(String modifiedby,int leaveid,String approve,String comment) throws DAOException{
		try {
			openConnection();
			String sql = "UPDATE `leaveapp`.`leave` SET remarks='" + comment
					+ "',`status`='" + approve + "',`modifiedBy`='"+modifiedby+"',`modifiedOn`=now() WHERE `leave_id`=" + leaveid
					+";";
			int i = st.executeUpdate(sql);
			System.out.println("SQL: " + sql);
			System.out.println("Updated rows: " + i);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new leaveSystem.dao.DAOException(error);
		} finally {
			try {
				closeConnection();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	public ArrayList<EmpLeave> getEmployeeLeaveHistory(int mgrid)
			throws DAOException {
		ArrayList<EmpLeave> empleaves = new ArrayList<EmpLeave>();
		try {
			openConnection();
			String sql = " SELECT l.*,lt.`leaveTypename`,e.`name` ";
			sql += "FROM `leaveapp`.`leave` l ";
			sql += "INNER JOIN `leaveapp`.`leave_type` lt ON l.`leave_type_id`=lt.`leaveTypeid` ";
			sql += "INNER JOIN `leaveapp`.`employee` e ON l.`employee_id`=e.`employeeid` ";
			sql += "WHERE e.`manager_id`=" + mgrid + " ";
			sql += "AND l.`leave_startDate`<= now() ";
			sql+="GROUP BY l.`employee_id` ORDER BY e.`name`;";
			System.out.println("SQL: " + sql);
			executeQuery(empleaves, sql);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new leaveSystem.dao.DAOException(error);
		} finally {
			try {
				closeConnection();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return empleaves;
	}

	public ArrayList<EmpLeave> getPersonalLeaveHistory(
			String operator, int empid) throws DAOException {
		ArrayList<EmpLeave> empleaves = new ArrayList<EmpLeave>();
		String addcond="";
		try {
			openConnection();
			if(operator.equals(">")){
				addcond+="AND l.`status` != 'Rejected' AND l.`status` != 'Deleted' AND l.`status` != 'Cancel' ";
			}
			String presql = "SELECT l.*,lt.`leaveTypename`,e.`name` ";
			presql+="FROM `leaveapp`.`leave` l ";
			presql+="INNER JOIN `leaveapp`.`leave_type` lt ";
			presql+="ON l.`leave_type_id`=lt.`leaveTypeid` ";
			presql+="INNER JOIN `leaveapp`.`employee` e ";
			presql+="ON l.`employee_id`=e.`employeeid` ";
			presql+="WHERE Year(l.`leave_startDate`)= Year(now()) ";
			presql+="AND l.`leave_startDate` "+operator+" now() ";
			presql+=addcond;
			presql+="AND `employee_id`="+empid+";";
			
			//System.out.println("SQL: "+presql);
			executeQuery(empleaves, presql);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new leaveSystem.dao.DAOException(error);
		} finally {
			try {
				closeConnection();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return empleaves;
	}

	// Execute Query for a ResultSet and return an ArrayList<EmpLeave>
	private void executeQuery(ArrayList<EmpLeave> leavesonsameperiod, String sql)
			throws SQLException {
		ResultSet rs = st.executeQuery(sql);
		while (rs.next()) {
			EmpLeave nempleave = new EmpLeave();
			nempleave.setLeaveId(rs.getInt("leave_id"));
			nempleave.setEmployeeId(rs.getInt("employee_id"));
			nempleave.setEmployeeName(rs.getString("name"));
			nempleave.setLeaveType(rs.getString("leaveTypename"));
			nempleave.setLeaveStartDate(rs.getDate("leave_startDate"));
			nempleave.setLeaveEndDate(rs.getDate("leave_endDate"));
			nempleave.setLeaveReason(rs.getString("leave_reason"));
			nempleave.setStatus(rs.getString("status"));
			nempleave.setRemainingLeaves(rs.getInt("remaining_leaves"));
			System.out.println(nempleave);
			leavesonsameperiod.add(nempleave);
		}
	}
}
